﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.IO;
using System.Text;
using System.Xml;

namespace Tools.MAME
{
    ///-------------------------------------------------------------------------------------------------
    /// <summary> Class used to create and interact with a SQLite database containing all knwon game
    /// information for a specific MAME revision. </summary>
    ///-------------------------------------------------------------------------------------------------
    public class GameDatabase : IDisposable
    {
        private enum SimpleDatatype
        {
            PrimaryText,
            Text,
            TextNotNull,
            Int,
            Boolean,
        }

        #region Member Variables

        private SQLiteConnection m_connection;
        private String m_databaseFile;

        #endregion

        #region Properties

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the connection NOTE: USE WITH CAUTION!!! </summary>
        /// <value> The connection. </value>
        ///-------------------------------------------------------------------------------------------------
        public SQLiteConnection Connection { get { return m_connection; } }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the number of games in the database. </summary>
        /// <value> The count. </value>
        ///-------------------------------------------------------------------------------------------------
        public int GameCount { get { return GetCount(); } }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the size of database as a formatted string for display purposes. </summary>
        /// <value> The size of the database. </value>
        ///-------------------------------------------------------------------------------------------------
        public Int64 SizeInBytes
        {
            get
            {
                if (m_databaseFile != null)
                    if (File.Exists(m_databaseFile))
                        return (new FileInfo(m_databaseFile)).Length;

                return 0;
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the date/time database was last modified as a formatted string for display
        /// purposes. </summary>
        /// <value> The UTC last modification time. </value>
        ///-------------------------------------------------------------------------------------------------
        public DateTime? LastModifiedTimeUTC
        {
            get
            {
                if (m_databaseFile != null)
                    if (File.Exists(m_databaseFile))
                        return (new FileInfo(m_databaseFile)).LastWriteTimeUtc;

                return null;
            }
        }

        #endregion

        #region Events

        /// <summary> Additional information for status message events. </summary>
        public class StatusMessageEventArgs : EventArgs
        {
            ///-------------------------------------------------------------------------------------------------
            /// <summary> Gets or sets the message. </summary>
            /// <value> The message. </value>
            ///-------------------------------------------------------------------------------------------------
            public String Message { get; private set; }

            ///-------------------------------------------------------------------------------------------------
            /// <summary> Constructor. </summary>
            /// <param name="message"> The message. </param>
            ///-------------------------------------------------------------------------------------------------
            public StatusMessageEventArgs(String message)
            {
                Message = message;
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Event queue for all listeners interested in StatusMessage events. </summary>
        ///-------------------------------------------------------------------------------------------------
        public event EventHandler<StatusMessageEventArgs> StatusMessage;

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Raises the status message event. </summary>
        /// <param name="e"> Event information to send to registered event handlers. </param>
        ///-------------------------------------------------------------------------------------------------
        private void OnStatusMessage(StatusMessageEventArgs e)
        {
            if (StatusMessage != null)
                StatusMessage(this, e);
        }

        #endregion

        #region Constructors

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Constructor. </summary>
        ///-------------------------------------------------------------------------------------------------
        public GameDatabase()
        {
            m_connection = new SQLiteConnection();
            m_databaseFile = null;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Performs application-defined tasks associated with freeing, releasing, or resetting
        /// unmanaged resources. </summary>
        /// <param name="disposing"> true to release both managed and unmanaged resources; false to
        /// release only unmanaged resources. </param>
        ///-------------------------------------------------------------------------------------------------
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (m_connection != null)
                {
                    m_connection.Dispose();
                    m_connection = null;
                }
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Performs application-defined tasks associated with freeing, releasing, or resetting
        /// unmanaged resources. </summary>
        ///-------------------------------------------------------------------------------------------------
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion

        #region Public Methods

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Connects to the database. </summary>
        /// <exception cref="ArgumentNullException"> Thrown when one or more required arguments are null. </exception>
        /// <param name="databaseFile"> Database file to connect to. </param>
        /// <returns> The DbConst.ConnectionStatus. </returns>
        ///-------------------------------------------------------------------------------------------------
        public ConnectionStatus Connect(String databaseFile)
        {
            if (databaseFile == null)
                throw new ArgumentNullException("databaseFile");

            m_databaseFile = databaseFile;

            try
            {
                // Create directory if necessary.
                if (!Directory.Exists(Path.GetDirectoryName(m_databaseFile)))
                    Directory.CreateDirectory(Path.GetDirectoryName(m_databaseFile));

                // Remember if database file already exists.
                bool dbExists = File.Exists(databaseFile);

                OnStatusMessage(new StatusMessageEventArgs("Opening connection..."));
                m_connection.ConnectionString = GetConnectionString(m_databaseFile);
                m_connection.Open();

                if (CheckAndRebuildSchema())
                {
                    if (dbExists)
                        return ConnectionStatus.RequiresRefresh;
                    else
                        return ConnectionStatus.New;
                }
                else
                    return ConnectionStatus.Success;
            }
            catch
            {
                m_databaseFile = null;
                throw;
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Clears the database. </summary>
        ///-------------------------------------------------------------------------------------------------
        public void Clear()
        {
            // Re-generate schema.
            BuildSchema();
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets available categories. </summary>
        /// <exception cref="Exception"> Thrown when an exception error condition occurs. </exception>
        /// <returns> The available categories. </returns>
        ///-------------------------------------------------------------------------------------------------
        public ICollection<String> GetAvailableCategories()
        {
            List<String> categories = new List<string>();

            String sqlQuery = String.Format("select [{0}] from [{1}] group by [{0}]",
                SqlConst.CATEGORY_COL, SqlConst.GAME_TABLE);

            using (SQLiteCommand command = new SQLiteCommand(sqlQuery, m_connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.IsDBNull(0))
                            throw new Exception("Null categories not supported.");

                        categories.Add(reader.GetValue(0).ToString());
                    }
                }
            }

            return categories;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets available regions. </summary>
        /// <exception cref="Exception"> Thrown when an exception error condition occurs. </exception>
        /// <returns> The available regions. </returns>
        ///-------------------------------------------------------------------------------------------------
        public ICollection<String> GetAvailableRegions()
        {
            List<String> regions = new List<string>();

            String sqlQuery = String.Format("select [{0}] from [{1}] group by [{0}]",
                SqlConst.REGION_COL, SqlConst.GAME_TABLE);

            using (SQLiteCommand command = new SQLiteCommand(sqlQuery, m_connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.IsDBNull(0))
                            throw new Exception("Null regions not supported.");

                        regions.Add(reader.GetValue(0).ToString());
                    }
                }
            }

            return regions;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets available controls. </summary>
        /// <exception cref="Exception"> Thrown when an exception error condition occurs. </exception>
        /// <returns> The available controls. </returns>
        ///-------------------------------------------------------------------------------------------------
        public ICollection<String> GetAvailableControls()
        {
            List<String> controls = new List<string>();

            String sqlQuery = String.Format("select [{0}] from [{1}] group by [{0}]",
                SqlConst.TYPE_COL, SqlConst.CONTROL_TABLE);

            using (SQLiteCommand command = new SQLiteCommand(sqlQuery, m_connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.IsDBNull(0))
                            throw new Exception("Null controls not supported.");

                        controls.Add(reader.GetValue(0).ToString());
                    }
                }
            }

            return controls;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Executes the supplied query on the database. </summary>
        /// <param name="sqlQuery"> The SQL query. </param>
        /// <returns> A DataTable filled with results of query. </returns>
        ///-------------------------------------------------------------------------------------------------
        public DataTable GetData(String sqlQuery)
        {
            if (sqlQuery == null)
                throw new ArgumentNullException("sqlQuery");

            try
            {
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, m_connection))
                    adapter.Fill(table);

                return table;
            }
            catch (Exception err)
            {
                throw new Exception(String.Format("Failure running query: {0}", sqlQuery), err);
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets database items that match specified filter. </summary>
        /// <param name="filter"> The filter. </param>
        /// <returns> The database items. </returns>
        ///-------------------------------------------------------------------------------------------------
        public List<DbItem> GetDbItems(DatabaseFilter filter)
        {
            List<String> fields = new List<string>();
            fields.Add(SqlConst.GAME_NAME_COL);
            fields.Add(SqlConst.DESCRIPTION_COL);
            fields.Add(SqlConst.CLONE_OF_COL);
            fields.Add(SqlConst.REGION_COL);
            fields.Add(SqlConst.YEAR_COL);
            fields.Add(SqlConst.MANUFACTURER_COL);
            fields.Add(SqlConst.INPUT_PLAYERS_COL);
            fields.Add(SqlConst.INPUT_BUTTONS_COL);
            fields.Add(SqlConst.DRIVER_STATUS_COL);
            fields.Add(SqlConst.CATEGORY_COL);
            fields.Add(SqlConst.SUB_CATEGORY_COL);
            fields.Add(SqlConst.IS_MATURE_COL);
            fields.Add(SqlConst.CHD_REQUIRED);

            List<DbItem> items = new List<DbItem>();

            // Run query (assumes db is locked in prior method.)
            using (DataTable table = GetData(fields.ToArray(), filter))
            {
                foreach (DataRow row in table.Rows)
                {
                    DbItem item = new DbItem();

                    item.Name = row.IsNull(0) ? "" : row[0].ToString();
                    item.Description = row.IsNull(1) ? "" : row[1].ToString();
                    item.CloneOf = row.IsNull(2) ? "" : row[2].ToString();
                    item.Region = row.IsNull(3) ? "" : row[3].ToString();
                    item.Year = row.IsNull(4) ? (int?)null : Convert.ToInt32(row[4]);
                    item.Manufacturer = row.IsNull(5) ? "" : row[5].ToString();
                    item.PlayerCount = row.IsNull(6) ? (int?)null : Convert.ToInt32(row[6]);
                    item.ButtonCount = row.IsNull(7) ? (int?)null : Convert.ToInt32(row[7]);
                    item.Status = row.IsNull(8) ? "" : row[8].ToString();
                    item.Category = row.IsNull(9) ? "" : row[9].ToString();
                    item.SubCategory = row.IsNull(10) ? "" : row[10].ToString();

                    if (row.IsNull(11))
                        item.IsMature = null;
                    else
                        item.IsMature = (Convert.ToInt32(row[11]) == 1) ? true : false;

                    item.CHDRequired = (Convert.ToInt32(row[12]) == 1) ? true : false;

                    items.Add(item);
                }
            }

            return items;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Executes the supplied query on the database, against the game table. </summary>
        /// <exception cref="Exception"> Thrown when an exception error condition occurs. </exception>
        /// <param name="gameTableFields"> The game table fields to retrieve. </param>
        /// <param name="filter">          (Optional) Specifies the filter. </param>
        /// <returns> A DataTable filled with results of query. </returns>
        ///-------------------------------------------------------------------------------------------------
        public DataTable GetData(String[] gameTableFields, DatabaseFilter filter = null)
        {
            if ((gameTableFields == null) || (gameTableFields.Length <= 0))
                throw new Exception("No fields were supplied.");

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select [{0}]", gameTableFields[0]);
            for (int i = 1; i < gameTableFields.Length; i++)
                sb.AppendFormat(", [{0}]", gameTableFields[i]);

            sb.AppendFormat(" from [{0}]", SqlConst.GAME_TABLE);

            if (filter != null)
            {
                if (filter.FilterMode == IntelligentFilterMode.OnePerWithRegionHint)
                {
                    String tempTableName = OnePerHelper.Process(m_connection, filter);
                    sb.AppendFormat(" where [{0}] in (select [{0}] from [{1}])",
                        SqlConst.GAME_NAME_COL, tempTableName);
                }
                else
                {
                    String whereClause = filter.BasicSQL;
                    if (!String.IsNullOrWhiteSpace(whereClause))
                        sb.AppendFormat(" where {0}", whereClause);
                }
            }

            return GetData(sb.ToString());
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Loads list from specified file. </summary>
        /// <exception cref="ArgumentNullException"> Thrown when one or more required arguments are null. </exception>
        /// <exception cref="FileNotFoundException"> Thrown when the requested file is not present. </exception>
        /// <exception cref="Exception">             Thrown when an exception error condition occurs. </exception>
        /// <param name="listXmlFile">  The file to load from. </param>
        /// <param name="categoryInfo"> Information describing the category. </param>
        /// <param name="index">        True to apply a number of useful indexes. Increases speed of
        /// certain queries, but also increases load time and size of database on disk. </param>
        ///-------------------------------------------------------------------------------------------------
        public void Load(String listXmlFile, CatVerList categoryInfo, bool index)
        {
            if (listXmlFile == null)
                throw new ArgumentNullException("listXmlFile");

            // Make sure specified file exists.
            if (!File.Exists(listXmlFile))
                throw new FileNotFoundException("Unable to locate specified file.", listXmlFile);

            OnStatusMessage(new StatusMessageEventArgs("Loading xml..."));

            // Load xml.
            XmlDocument document = new XmlDocument();
            using (StreamReader sr = new StreamReader(listXmlFile))
                document.Load(sr);

            OnStatusMessage(new StatusMessageEventArgs("Building db..."));

            // Retrieve root node.
            XmlNode rootNode = document.SelectSingleNode(XmlConst.MAME_NODE);

            // Verify version.
            int configVersion = Convert.ToInt32(rootNode.Attributes[XmlConst.MAME_CONFIG_ATTR].Value);
            if (configVersion != XmlConst.CONFIG_VERSION)
                throw new Exception(String.Format("Version mismatch. Expected: [{0}] Found: [{1}]",
                    XmlConst.CONFIG_VERSION, configVersion));

            // Generate transaction.
            SQLiteTransaction transaction = m_connection.BeginTransaction();

            try
            {
                // Generate command object for each table.
                SQLiteCommand buildCommand = GetInsertCommand(SqlConst.BUILD_TABLE, SqlConst.BUILD_COLUMN_COUNT, transaction);
                SQLiteCommand gamesCommand = GetInsertCommand(SqlConst.GAME_TABLE, SqlConst.GAMES_COLUMN_COUNT, transaction);
                SQLiteCommand parentCommand = GetInsertCommand(SqlConst.PARENT_TABLE, SqlConst.PARENT_TABLE_COLUMN_COUNT, transaction);
                SQLiteCommand childCommand = GetInsertCommand(SqlConst.CLONE_TABLE, SqlConst.CLONE_TABLE_COLUMN_COUNT, transaction);
                SQLiteCommand biossetCommand = GetInsertCommand(SqlConst.BIOSSET_TABLE, SqlConst.BIOSSET_COLUMN_COUNT, transaction);
                SQLiteCommand romsCommand = GetInsertCommand(SqlConst.ROM_TABLE, SqlConst.ROMS_COLUMN_COUNT, transaction);
                SQLiteCommand disksCommand = GetInsertCommand(SqlConst.DISK_TABLE, SqlConst.DISKS_COLUMN_COUNT, transaction);
                SQLiteCommand deviceRefsCommand = GetInsertCommand(SqlConst.DEVICE_REF_TABLE, SqlConst.DEVICE_REFS_COLUMN_COUNT, transaction);
                SQLiteCommand samplesCommand = GetInsertCommand(SqlConst.SAMPLE_TABLE, SqlConst.SAMPLES_COLUMN_COUNT, transaction);
                SQLiteCommand chipsCommand = GetInsertCommand(SqlConst.CHIP_TABLE, SqlConst.CHIPS_COLUMN_COUNT, transaction);
                SQLiteCommand displayCommand = GetInsertCommand(SqlConst.DISPLAY_TABLE, SqlConst.DISPLAY_COLUMN_COUNT, transaction);
                SQLiteCommand controlsCommand = GetInsertCommand(SqlConst.CONTROL_TABLE, SqlConst.CONTROLS_COLUMN_COUNT, transaction);
                SQLiteCommand dipsCommand = GetInsertCommand(SqlConst.DIP_TABLE, SqlConst.DIPS_COLUMN_COUNT, transaction);
                SQLiteCommand dipValuesCommand = GetInsertCommand(SqlConst.DIP_VALUE_TABLE, SqlConst.DIP_VALUES_COLUMN_COUNT, transaction);
                SQLiteCommand configCommand = GetInsertCommand(SqlConst.CONFIG_TABLE, SqlConst.CONFIG_COLUMN_COUNT, transaction);
                SQLiteCommand configSettingCommand = GetInsertCommand(SqlConst.CONFIG_SETTING_TABLE, SqlConst.CONFIG_SETTING_COLUMN_COUNT, transaction);
                SQLiteCommand portsCommand = GetInsertCommand(SqlConst.PORT_TABLE, SqlConst.PORTS_COLUMN_COUNT, transaction);
                SQLiteCommand adjusterCommand = GetInsertCommand(SqlConst.ADJUSTER_TABLE, SqlConst.ADJUSTER_COLUMN_COUNT, transaction);
                SQLiteCommand devicesCommand = GetInsertCommand(SqlConst.DEVICE_TABLE, SqlConst.DEVICES_COLUMN_COUNT, transaction);
                SQLiteCommand slotCommand = GetInsertCommand(SqlConst.SLOT_TABLE, SqlConst.SLOT_TABLE_COLUMN_COUNT, transaction);
                SQLiteCommand softwareListsCommand = GetInsertCommand(SqlConst.SOFTWARE_LIST_TABLE, SqlConst.SOFTWARE_LISTS_COLUMN_COUNT, transaction);
                SQLiteCommand ramOptionsCommand = GetInsertCommand(SqlConst.RAM_OPTION_TABLE, SqlConst.RAM_OPTIONS_COLUMN_COUNT, transaction);

                try
                {
                    // 
                    // Build
                    //

                    buildCommand.Parameters[0].Value = rootNode.Attributes[XmlConst.BUILD_ATTR].Value;
                    buildCommand.Parameters[1].Value = ConvertYesNo(rootNode.Attributes[XmlConst.DEBUG_ATTR].Value);
                    buildCommand.Parameters[2].Value = configVersion;
                    buildCommand.ExecuteNonQuery();

                    // Loop through games.
                    foreach (XmlNode gameNode in rootNode.SelectNodes(XmlConst.GAME_NODE))
                    {
                        String romName = gameNode.Attributes[XmlConst.NAME_ATTR].Value;

                        String cloneOf = null;
                        if (gameNode.Attributes[XmlConst.CLONE_OF_ATTR] != null)
                            cloneOf = gameNode.Attributes[XmlConst.CLONE_OF_ATTR].Value;

                        //
                        // Game
                        // 

                        gamesCommand.Parameters[0].Value = romName;
                        gamesCommand.Parameters[1].Value = gameNode.Attributes[XmlConst.SOURCE_FILE_ATTR].Value; // IsRequired
                        gamesCommand.Parameters[2].Value = ConvertYesNo(gameNode.Attributes[XmlConst.IS_BIOS_ATTR].Value); // HasDefault
                        gamesCommand.Parameters[3].Value = ConvertYesNo(gameNode.Attributes[XmlConst.IS_DEVICE_ATTR].Value); // HasDefault
                        gamesCommand.Parameters[4].Value = ConvertYesNo(gameNode.Attributes[XmlConst.IS_MECHANICAL_ATTR].Value); // HasDefault
                        gamesCommand.Parameters[5].Value = ConvertYesNo(gameNode.Attributes[XmlConst.RUNNABLE_ATTR].Value); // HasDefault
                        gamesCommand.Parameters[6].Value = cloneOf;
                        gamesCommand.Parameters[7].Value = (gameNode.Attributes[XmlConst.ROM_OF_ATTR] == null) ? null : gameNode.Attributes[XmlConst.ROM_OF_ATTR].Value;
                        gamesCommand.Parameters[8].Value = (gameNode.Attributes[XmlConst.SAMPLE_OF_ATTR_] == null) ? null : gameNode.Attributes[XmlConst.SAMPLE_OF_ATTR_].Value;
                        gamesCommand.Parameters[9].Value = gameNode.SelectSingleNode(XmlConst.DESCRIPTION_NODE).InnerText; // IsRequired
                        gamesCommand.Parameters[10].Value = (gameNode.SelectSingleNode(XmlConst.YEAR_NODE) == null) ? null : ParseYear(gameNode.SelectSingleNode(XmlConst.YEAR_NODE).InnerText);
                        gamesCommand.Parameters[11].Value = (gameNode.SelectSingleNode(XmlConst.MANUFACTURER_NODE) == null) ? null : gameNode.SelectSingleNode(XmlConst.MANUFACTURER_NODE).InnerText;

                        XmlNode soundNode = gameNode.SelectSingleNode(XmlConst.SOUND_NODE);
                        gamesCommand.Parameters[12].Value = (soundNode == null) ? null : soundNode.Attributes[XmlConst.CHANNELS_ATTR].Value;

                        XmlNode inputNode = gameNode.SelectSingleNode(XmlConst.INPUT_NODE);
                        if (inputNode == null)
                        {
                            gamesCommand.Parameters[13].Value = null;
                            gamesCommand.Parameters[14].Value = null;
                            gamesCommand.Parameters[15].Value = null;
                            gamesCommand.Parameters[16].Value = null;
                            gamesCommand.Parameters[17].Value = null;
                        }
                        else
                        {
                            gamesCommand.Parameters[13].Value = ConvertYesNo(inputNode.Attributes[XmlConst.SERVICE_ATTR].Value); // HasDefault
                            gamesCommand.Parameters[14].Value = ConvertYesNo(inputNode.Attributes[XmlConst.TILT_ATTR].Value); // HasDefault
                            gamesCommand.Parameters[15].Value = (inputNode.Attributes[XmlConst.PLAYERS_ATTR] == null) ? null : inputNode.Attributes[XmlConst.PLAYERS_ATTR].Value;
                            gamesCommand.Parameters[16].Value = (inputNode.Attributes[XmlConst.BUTTONS_ATTR] == null) ? 0 : Convert.ToInt32(inputNode.Attributes[XmlConst.BUTTONS_ATTR].Value);
                            gamesCommand.Parameters[17].Value = (inputNode.Attributes[XmlConst.COINS_ATTR] == null) ? null : inputNode.Attributes[XmlConst.COINS_ATTR].Value;
                        }

                        XmlNode driverNode = gameNode.SelectSingleNode(XmlConst.DRIVER_NODE);
                        if (driverNode == null)
                        {
                            gamesCommand.Parameters[18].Value = null;
                            gamesCommand.Parameters[19].Value = null;
                            gamesCommand.Parameters[20].Value = null;
                            gamesCommand.Parameters[21].Value = null;
                            gamesCommand.Parameters[22].Value = null;
                            gamesCommand.Parameters[23].Value = null;
                            gamesCommand.Parameters[24].Value = null;
                            gamesCommand.Parameters[25].Value = null;
                            gamesCommand.Parameters[26].Value = null;
                        }
                        else
                        {
                            gamesCommand.Parameters[18].Value = ConvertStatus(driverNode.Attributes[XmlConst.STATUS_ATTR].Value);
                            gamesCommand.Parameters[19].Value = ConvertStatus(driverNode.Attributes[XmlConst.EMULATION_ATTR].Value);
                            gamesCommand.Parameters[20].Value = ConvertStatus(driverNode.Attributes[XmlConst.COLOR_ATTR].Value);
                            gamesCommand.Parameters[21].Value = ConvertStatus(driverNode.Attributes[XmlConst.SOUND_ATTR].Value);
                            gamesCommand.Parameters[22].Value = ConvertStatus(driverNode.Attributes[XmlConst.GRAPHIC_ATTR].Value);

                            if (driverNode.Attributes[XmlConst.COCKTAIL_ATTR] == null)
                                gamesCommand.Parameters[23].Value = null;
                            else
                                gamesCommand.Parameters[23].Value = ConvertStatus(driverNode.Attributes[XmlConst.COCKTAIL_ATTR].Value);

                            if (driverNode.Attributes[XmlConst.PROTECTION_ATTR] == null)
                                gamesCommand.Parameters[24].Value = null;
                            else
                                gamesCommand.Parameters[24].Value = ConvertStatus(driverNode.Attributes[XmlConst.PROTECTION_ATTR].Value);

                            gamesCommand.Parameters[25].Value = ConvertSupported(driverNode.Attributes[XmlConst.SAVESTATE_ATTR].Value);
                            gamesCommand.Parameters[26].Value = driverNode.Attributes[XmlConst.PALETTESIZE_ATTR].Value;
                        }

                        CatVerItemInfo itemInfo = (categoryInfo != null) ? categoryInfo[romName] : null;
                        if (itemInfo == null)
                        {
                            gamesCommand.Parameters[27].Value = CatVerList.NOT_CLASSIFIED;
                            gamesCommand.Parameters[28].Value = null;
                            gamesCommand.Parameters[29].Value = null;
                            gamesCommand.Parameters[30].Value = null;
                        }
                        else
                        {
                            gamesCommand.Parameters[27].Value = String.IsNullOrWhiteSpace(itemInfo.Category) ? CatVerList.NOT_CLASSIFIED : itemInfo.Category;
                            gamesCommand.Parameters[28].Value = itemInfo.SubCategory;
                            gamesCommand.Parameters[29].Value = itemInfo.IsMature;
                            gamesCommand.Parameters[30].Value = itemInfo.VersionAdded;
                        }

                        //
                        // Disks (Runs before we complete game so that we can retrieve chdrequired setting.
                        // 

                        bool chdRequired = false;
                        foreach (XmlNode diskNode in gameNode.SelectNodes(XmlConst.DISK_NODE))
                        {
                            bool isOptional = ConvertYesNo(diskNode.Attributes[XmlConst.OPTIONAL_ATTR].Value); // HasDefault
                            if (!isOptional)
                                chdRequired = true;

                            disksCommand.Parameters[0].Value = romName;
                            disksCommand.Parameters[1].Value = diskNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            disksCommand.Parameters[2].Value = (diskNode.Attributes[XmlConst.SHA1_ATTR] == null) ? null : diskNode.Attributes[XmlConst.SHA1_ATTR].Value;
                            disksCommand.Parameters[3].Value = (diskNode.Attributes[XmlConst.MERGE_ATTR] == null) ? null : diskNode.Attributes[XmlConst.MERGE_ATTR].Value;
                            disksCommand.Parameters[4].Value = (diskNode.Attributes[XmlConst.REGION_ATTR] == null) ? null : diskNode.Attributes[XmlConst.REGION_ATTR].Value;
                            disksCommand.Parameters[5].Value = (diskNode.Attributes[XmlConst.INDEX_ATTR] == null) ? null : diskNode.Attributes[XmlConst.INDEX_ATTR].Value;
                            disksCommand.Parameters[6].Value = ConvertYesNo(diskNode.Attributes[XmlConst.WRITABLE_ATTR].Value); // HasDefault
                            disksCommand.Parameters[7].Value = ConvertRomDumpStatus(diskNode.Attributes[XmlConst.STATUS_ATTR].Value); // HasDefault
                            disksCommand.Parameters[8].Value = isOptional;
                            disksCommand.ExecuteNonQuery();
                        }

                        gamesCommand.Parameters[31].Value = chdRequired;
                        gamesCommand.Parameters[32].Value = null; // Region. Supplied later.
                        gamesCommand.ExecuteNonQuery();

                        //
                        // Parent / Clone
                        // 

                        if (String.IsNullOrWhiteSpace(cloneOf))
                        {
                            parentCommand.Parameters[0].Value = romName;
                            parentCommand.ExecuteNonQuery();
                        }
                        else
                        {
                            childCommand.Parameters[0].Value = romName;
                            childCommand.Parameters[1].Value = cloneOf;
                            childCommand.ExecuteNonQuery();
                        }

                        //
                        // Biosset
                        //

                        foreach (XmlNode biossetNode in gameNode.SelectNodes(XmlConst.BIOSSET_NODE))
                        {
                            biossetCommand.Parameters[0].Value = romName;
                            biossetCommand.Parameters[1].Value = biossetNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            biossetCommand.Parameters[2].Value = biossetNode.Attributes[XmlConst.DESCRIPTION_NODE].Value; // IsRequired
                            biossetCommand.Parameters[3].Value = ConvertYesNo(biossetNode.Attributes[XmlConst.DEFAULT_ATTR].Value); // HasDefault
                            biossetCommand.ExecuteNonQuery();
                        }

                        //
                        // Roms
                        // 

                        foreach (XmlNode romNode in gameNode.SelectNodes(XmlConst.ROM_NODE))
                        {
                            romsCommand.Parameters[0].Value = romName;
                            romsCommand.Parameters[1].Value = romNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            romsCommand.Parameters[2].Value = (romNode.Attributes[XmlConst.BIOS_ATTR] == null) ? null : romNode.Attributes[XmlConst.BIOS_ATTR].Value;
                            romsCommand.Parameters[3].Value = romNode.Attributes[XmlConst.SIZE_ATTR].Value; // IsRequired
                            romsCommand.Parameters[4].Value = (romNode.Attributes[XmlConst.CRC_ATTR] == null) ? null : romNode.Attributes[XmlConst.CRC_ATTR].Value;
                            romsCommand.Parameters[5].Value = (romNode.Attributes[XmlConst.SHA1_ATTR] == null) ? null : romNode.Attributes[XmlConst.SHA1_ATTR].Value;
                            romsCommand.Parameters[6].Value = (romNode.Attributes[XmlConst.MERGE_ATTR] == null) ? null : romNode.Attributes[XmlConst.MERGE_ATTR].Value;
                            romsCommand.Parameters[7].Value = (romNode.Attributes[XmlConst.REGION_ATTR] == null) ? null : romNode.Attributes[XmlConst.REGION_ATTR].Value;
                            romsCommand.Parameters[8].Value = (romNode.Attributes[XmlConst.OFFSET_ATTR] == null) ? null : romNode.Attributes[XmlConst.OFFSET_ATTR].Value;
                            romsCommand.Parameters[9].Value = ConvertRomDumpStatus(romNode.Attributes[XmlConst.STATUS_ATTR].Value); // HasDefault
                            romsCommand.Parameters[10].Value = ConvertYesNo(romNode.Attributes[XmlConst.OPTIONAL_ATTR].Value); // HasDefault
                            romsCommand.ExecuteNonQuery();
                        }

                        //
                        // Device_refs
                        // 

                        foreach (XmlNode deviceRefNode in gameNode.SelectNodes(XmlConst.DEVICE_REF_NODE))
                        {
                            deviceRefsCommand.Parameters[0].Value = romName;
                            deviceRefsCommand.Parameters[1].Value = deviceRefNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            deviceRefsCommand.ExecuteNonQuery();
                        }

                        //
                        // Samples
                        // 

                        foreach (XmlNode sampleNode in gameNode.SelectNodes(XmlConst.SAMPLE_NODE))
                        {
                            samplesCommand.Parameters[0].Value = romName;
                            samplesCommand.Parameters[1].Value = sampleNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            samplesCommand.ExecuteNonQuery();
                        }

                        //
                        // Chips
                        // 

                        foreach (XmlNode chipNode in gameNode.SelectNodes(XmlConst.CHIP_NODE))
                        {
                            chipsCommand.Parameters[0].Value = romName;
                            chipsCommand.Parameters[1].Value = chipNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            chipsCommand.Parameters[2].Value = (chipNode.Attributes[XmlConst.TAG_ATTR] == null) ? null : chipNode.Attributes[XmlConst.TAG_ATTR].Value;
                            chipsCommand.Parameters[3].Value = ConvertChipType(chipNode.Attributes[XmlConst.TYPE_ATTR].Value); // IsRequired
                            chipsCommand.Parameters[4].Value = (chipNode.Attributes[XmlConst.CLOCK_ATTR] == null) ? null : chipNode.Attributes[XmlConst.CLOCK_ATTR].Value;
                            chipsCommand.ExecuteNonQuery();
                        }

                        //
                        // Display
                        // 

                        foreach (XmlNode displayNode in gameNode.SelectNodes(XmlConst.DISPLAY_NODE))
                        {
                            displayCommand.Parameters[0].Value = romName;
                            displayCommand.Parameters[1].Value = (displayNode.Attributes[XmlConst.TAG_ATTR] == null) ? null : displayNode.Attributes[XmlConst.TAG_ATTR].Value;
                            displayCommand.Parameters[2].Value = ConvertDisplayType(displayNode.Attributes[XmlConst.TYPE_ATTR].Value); // IsRequired
                            displayCommand.Parameters[3].Value = displayNode.Attributes[XmlConst.ROTATE_ATTR].Value; // IsRequired
                            displayCommand.Parameters[4].Value = ConvertYesNo(displayNode.Attributes[XmlConst.FLIPX_ATTR].Value); // HasDefault
                            displayCommand.Parameters[5].Value = (displayNode.Attributes[XmlConst.WIDTH_ATTR] == null) ? null : displayNode.Attributes[XmlConst.WIDTH_ATTR].Value;
                            displayCommand.Parameters[6].Value = (displayNode.Attributes[XmlConst.HEIGHT_ATTR] == null) ? null : displayNode.Attributes[XmlConst.HEIGHT_ATTR].Value;
                            displayCommand.Parameters[7].Value = displayNode.Attributes[XmlConst.REFRESH_ATTR].Value; // IsRequired
                            displayCommand.Parameters[8].Value = (displayNode.Attributes[XmlConst.PIXCLOCK_ATTR] == null) ? null : displayNode.Attributes[XmlConst.PIXCLOCK_ATTR].Value;
                            displayCommand.Parameters[9].Value = (displayNode.Attributes[XmlConst.HTOTAL_ATTR] == null) ? null : displayNode.Attributes[XmlConst.HTOTAL_ATTR].Value;
                            displayCommand.Parameters[10].Value = (displayNode.Attributes[XmlConst.HBEND_ATTR] == null) ? null : displayNode.Attributes[XmlConst.HBEND_ATTR].Value;
                            displayCommand.Parameters[11].Value = (displayNode.Attributes[XmlConst.HBSTART_ATTR] == null) ? null : displayNode.Attributes[XmlConst.HBSTART_ATTR].Value;
                            displayCommand.Parameters[12].Value = (displayNode.Attributes[XmlConst.VTOTAL_ATTR] == null) ? null : displayNode.Attributes[XmlConst.VTOTAL_ATTR].Value;
                            displayCommand.Parameters[13].Value = (displayNode.Attributes[XmlConst.VBEND_ATTR] == null) ? null : displayNode.Attributes[XmlConst.VBEND_ATTR].Value;
                            displayCommand.Parameters[14].Value = (displayNode.Attributes[XmlConst.VBSTART_ATTR] == null) ? null : displayNode.Attributes[XmlConst.VBSTART_ATTR].Value;
                            displayCommand.ExecuteNonQuery();
                        }

                        //
                        // Controls
                        // 

                        if (inputNode != null)
                        {
                            foreach (XmlNode controlNode in inputNode.SelectNodes(XmlConst.CONTROL_NODE))
                            {
                                controlsCommand.Parameters[0].Value = romName;
                                controlsCommand.Parameters[1].Value = controlNode.Attributes[XmlConst.TYPE_ATTR].Value;
                                controlsCommand.Parameters[2].Value = (controlNode.Attributes[XmlConst.MINIMUM_ATTR] == null) ? null : controlNode.Attributes[XmlConst.MINIMUM_ATTR].Value;
                                controlsCommand.Parameters[3].Value = (controlNode.Attributes[XmlConst.MAXIMUM_ATTR] == null) ? null : controlNode.Attributes[XmlConst.MAXIMUM_ATTR].Value;
                                controlsCommand.Parameters[4].Value = (controlNode.Attributes[XmlConst.SENSITIVITY_ATTR] == null) ? null : controlNode.Attributes[XmlConst.SENSITIVITY_ATTR].Value;
                                controlsCommand.Parameters[5].Value = (controlNode.Attributes[XmlConst.KEY_DELTA_ATTR] == null) ? null : controlNode.Attributes[XmlConst.KEY_DELTA_ATTR].Value;
                                controlsCommand.Parameters[6].Value = ConvertYesNo(controlNode.Attributes[XmlConst.REVERSE_ATTR].Value);
                                controlsCommand.Parameters[7].Value = (controlNode.Attributes[XmlConst.WAYS_ATTR] == null) ? null : controlNode.Attributes[XmlConst.WAYS_ATTR].Value;
                                controlsCommand.Parameters[8].Value = (controlNode.Attributes[XmlConst.WAYS2_ATTR] == null) ? null : controlNode.Attributes[XmlConst.WAYS2_ATTR].Value;
                                controlsCommand.Parameters[9].Value = (controlNode.Attributes[XmlConst.WAYS3_ATTR] == null) ? null : controlNode.Attributes[XmlConst.WAYS3_ATTR].Value;
                                controlsCommand.ExecuteNonQuery();
                            }
                        }

                        //
                        // Dips / Dip Values
                        // 

                        foreach (XmlNode dipswitchNode in gameNode.SelectNodes(XmlConst.DIPSWITCH_NODE))
                        {
                            String switchName = dipswitchNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired

                            dipsCommand.Parameters[0].Value = romName;
                            dipsCommand.Parameters[1].Value = switchName;
                            dipsCommand.Parameters[2].Value = dipswitchNode.Attributes[XmlConst.TAG_ATTR].Value; // IsRequired
                            dipsCommand.Parameters[3].Value = dipswitchNode.Attributes[XmlConst.MASK_ATTR].Value; // IsRequired
                            dipsCommand.ExecuteNonQuery();

                            foreach (XmlNode dipValueNode in dipswitchNode.SelectNodes(XmlConst.DIPVALUE_NODE))
                            {
                                dipValuesCommand.Parameters[0].Value = romName;
                                dipValuesCommand.Parameters[1].Value = switchName;
                                dipValuesCommand.Parameters[2].Value = dipValueNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                                dipValuesCommand.Parameters[3].Value = dipValueNode.Attributes[XmlConst.VALUE_ATTR].Value; // IsRequired
                                dipValuesCommand.Parameters[4].Value = ConvertYesNo(dipValueNode.Attributes[XmlConst.DEFAULT_ATTR].Value); // HasDefault
                                dipValuesCommand.ExecuteNonQuery();
                            }
                        }

                        //
                        // Configuration / Settings
                        // 

                        foreach (XmlNode configNode in gameNode.SelectNodes(XmlConst.CONFIGURATION_NODE))
                        {
                            String configName = configNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired

                            configCommand.Parameters[0].Value = romName;
                            configCommand.Parameters[1].Value = configName;
                            configCommand.Parameters[2].Value = configNode.Attributes[XmlConst.TAG_ATTR].Value; // IsRequired
                            configCommand.Parameters[3].Value = configNode.Attributes[XmlConst.MASK_ATTR].Value; // IsRequired
                            configCommand.ExecuteNonQuery();

                            foreach (XmlNode configSettingNode in configNode.SelectNodes(XmlConst.CONFIGURATION_SETTING_NODE))
                            {
                                configSettingCommand.Parameters[0].Value = romName;
                                configSettingCommand.Parameters[1].Value = configName;
                                configSettingCommand.Parameters[2].Value = configSettingNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                                configSettingCommand.Parameters[3].Value = configSettingNode.Attributes[XmlConst.VALUE_ATTR].Value; // IsRequired
                                configSettingCommand.Parameters[4].Value = ConvertYesNo(configSettingNode.Attributes[XmlConst.DEFAULT_ATTR].Value); // HasDefault
                                configSettingCommand.ExecuteNonQuery();
                            }
                        }

                        //
                        // Ports
                        // 

                        foreach (XmlNode portNode in gameNode.SelectNodes(XmlConst.PORT_NODE))
                        {
                            portsCommand.Parameters[0].Value = romName;
                            portsCommand.Parameters[1].Value = portNode.Attributes[XmlConst.TAG_ATTR].Value;
                            portsCommand.Parameters[2].Value = (portNode.SelectSingleNode(XmlConst.ANALOG_NODE) == null) ? null : portNode.SelectSingleNode(XmlConst.ANALOG_NODE).Attributes[XmlConst.MASK_ATTR].Value;
                            portsCommand.ExecuteNonQuery();
                        }

                        //
                        // Adjuster
                        // 

                        foreach (XmlNode adjusterNode in gameNode.SelectNodes(XmlConst.ADJUSTER_NODE))
                        {
                            adjusterCommand.Parameters[0].Value = romName;
                            adjusterCommand.Parameters[1].Value = adjusterNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            adjusterCommand.Parameters[2].Value = adjusterNode.Attributes[XmlConst.DEFAULT_ATTR].Value; // IsRequired
                            adjusterCommand.ExecuteNonQuery();
                        }

                        //
                        // Devices
                        //

                        foreach (XmlNode deviceNode in gameNode.SelectNodes(XmlConst.DEVICE_NODE))
                        {
                            devicesCommand.Parameters[0].Value = romName;
                            devicesCommand.Parameters[1].Value = deviceNode.Attributes[XmlConst.TYPE_ATTR].Value;
                            devicesCommand.Parameters[2].Value = deviceNode.Attributes[XmlConst.TAG_ATTR].Value;
                            devicesCommand.Parameters[3].Value = (deviceNode.Attributes[XmlConst.INTERFACE_ATTR] == null) ? null : deviceNode.Attributes[XmlConst.INTERFACE_ATTR].Value;

                            XmlNode instanceNode = deviceNode.SelectSingleNode(XmlConst.INSTANCE_NODE);
                            devicesCommand.Parameters[4].Value = instanceNode.Attributes[XmlConst.NAME_ATTR].Value;
                            devicesCommand.Parameters[5].Value = instanceNode.Attributes[XmlConst.BRIEF_NAME_ATTR].Value;

                            XmlNodeList extensions = deviceNode.SelectNodes(XmlConst.EXTENSION_NODE);
                            if (extensions.Count > 0)
                            {
                                StringBuilder extensionBuilder = new StringBuilder();

                                extensionBuilder.Append(extensions[0].Attributes[XmlConst.NAME_ATTR].Value);
                                for (int i = 1; i < extensions.Count; i++)
                                    extensionBuilder.AppendFormat(";{0}", extensions[i].Attributes[XmlConst.NAME_ATTR].Value);

                                devicesCommand.Parameters[6].Value = extensionBuilder.ToString();
                            }
                            else
                                devicesCommand.Parameters[6].Value = null;

                            devicesCommand.ExecuteNonQuery();
                        }

                        // 
                        // Slots
                        // 

                        foreach (XmlNode slotNode in gameNode.SelectNodes(XmlConst.SLOT_NODE))
                        {
                            slotCommand.Parameters[0].Value = romName;
                            slotCommand.Parameters[1].Value = slotNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired

                            foreach (XmlNode slotOptionNode in slotNode.SelectNodes(XmlConst.SLOT_OPTION_NODE))
                            {
                                slotCommand.Parameters[2].Value = slotOptionNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                                slotCommand.Parameters[3].Value = slotOptionNode.Attributes[XmlConst.DEV_NAME_ATTR].Value; // IsRequired
                                slotCommand.Parameters[4].Value = ConvertYesNo(slotOptionNode.Attributes[XmlConst.DEFAULT_ATTR].Value); // HasDefault
                                slotCommand.ExecuteNonQuery();
                            }
                        }

                        //
                        // Software Lists
                        // 

                        foreach (XmlNode softwareListNode in gameNode.SelectNodes(XmlConst.SOFTWARE_LIST_NODE))
                        {
                            softwareListsCommand.Parameters[0].Value = romName;
                            softwareListsCommand.Parameters[1].Value = softwareListNode.Attributes[XmlConst.NAME_ATTR].Value; // IsRequired
                            softwareListsCommand.Parameters[2].Value = ConvertSoftwareListStatus(softwareListNode.Attributes[XmlConst.STATUS_ATTR].Value); // IsRequired
                            softwareListsCommand.Parameters[3].Value = (softwareListNode.Attributes[XmlConst.FILTER_ATTR] == null) ? null : softwareListNode.Attributes[XmlConst.FILTER_ATTR].Value;
                            softwareListsCommand.ExecuteNonQuery();
                        }

                        //
                        // Ram Options
                        // 

                        foreach (XmlNode ramOptionNode in gameNode.SelectNodes(XmlConst.RAM_OPTION_NODE))
                        {
                            ramOptionsCommand.Parameters[0].Value = romName;
                            ramOptionsCommand.Parameters[1].Value = ramOptionNode.Attributes[XmlConst.DEFAULT_ATTR].Value; // ??
                            ramOptionsCommand.Parameters[2].Value = ramOptionNode.InnerText; // ??
                            ramOptionsCommand.ExecuteNonQuery();
                        }
                    } // End game loop.
                }
                finally
                {
                    // TODO: Dispose of all insert commands.
                }

                // Region
                OnStatusMessage(new StatusMessageEventArgs("Reticulating region splines..."));
                PopulateRegionColumn(transaction);

                if (index)
                {
                    // Indexing
                    CreateIndex(SqlConst.GAME_TABLE, SqlConst.CLONE_OF_COL, transaction);
                    CreateIndex(SqlConst.GAME_TABLE, SqlConst.DESCRIPTION_COL, transaction);
                    CreateIndex(SqlConst.GAME_TABLE, SqlConst.YEAR_COL, transaction);
                    CreateIndex(SqlConst.GAME_TABLE, SqlConst.CATEGORY_COL, transaction);

                    CreateIndex(SqlConst.BIOSSET_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.ROM_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.DISK_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.DEVICE_REF_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.SAMPLE_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.CHIP_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.DISPLAY_TABLE, SqlConst.GAME_NAME_COL, transaction);

                    CreateIndex(SqlConst.CONTROL_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.CONTROL_TABLE, SqlConst.TYPE_COL, transaction);

                    CreateIndex(SqlConst.DIP_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.DIP_VALUE_TABLE, new String[] { SqlConst.GAME_NAME_COL, SqlConst.DIP_NAME_COL }, transaction);
                    CreateIndex(SqlConst.CONFIG_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.CONFIG_SETTING_TABLE, new String[] { SqlConst.GAME_NAME_COL, SqlConst.CONFIG_NAME_COL }, transaction);
                    CreateIndex(SqlConst.PORT_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.ADJUSTER_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.DEVICE_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.SLOT_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.SOFTWARE_LIST_TABLE, SqlConst.GAME_NAME_COL, transaction);
                    CreateIndex(SqlConst.RAM_OPTION_TABLE, SqlConst.GAME_NAME_COL, transaction);
                }

                // Analyze
                using (SQLiteCommand analyzeCommand = new SQLiteCommand("analyze", m_connection, transaction))
                {
                    OnStatusMessage(new StatusMessageEventArgs("Analyzing..."));
                    analyzeCommand.ExecuteNonQuery();
                }

                transaction.Commit();
            }
            catch
            {
                try
                {
                    transaction.Rollback();
                }
                catch
                { }

                throw;
            }
        }

        #endregion

        #region Private Helper Methods

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Converts supplied string to SoftwareListStatus enum. </summary>
        /// <param name="value"> The value. </param>
        /// <returns> The matching enum. </returns>
        ///-------------------------------------------------------------------------------------------------
        private SoftwareListStatus ConvertSoftwareListStatus(String value)
        {
            return (SoftwareListStatus)Enum.Parse(typeof(SoftwareListStatus), value, true);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Convert display type. </summary>
        /// <param name="value"> The value. </param>
        /// <returns> The display converted type. </returns>
        ///-------------------------------------------------------------------------------------------------
        private DisplayType ConvertDisplayType(String value)
        {
            return (DisplayType)Enum.Parse(typeof(DisplayType), value, true);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Convert chip type. </summary>
        /// <param name="chipType"> Type of the chip. </param>
        /// <returns> The chip converted type. </returns>
        ///-------------------------------------------------------------------------------------------------
        private ChipType ConvertChipType(String chipType)
        {
            return (ChipType)Enum.Parse(typeof(ChipType), chipType, true);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Converts supplied string to DriverAttributeStatus enum. </summary>
        /// <param name="status"> The driver attribute status string. </param>
        /// <returns> The matching enum. </returns>
        ///-------------------------------------------------------------------------------------------------
        private DriverAttributeStatus ConvertStatus(String status)
        {
            return (DriverAttributeStatus)Enum.Parse(typeof(DriverAttributeStatus), status, true);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Converts supplied string to RomDumpStatus enum. </summary>
        /// <param name="status"> The rom dump status string. </param>
        /// <returns> The matching enum. </returns>
        ///-------------------------------------------------------------------------------------------------
        private RomDumpStatus ConvertRomDumpStatus(String status)
        {
            return (RomDumpStatus)Enum.Parse(typeof(RomDumpStatus), status, true);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Convert yes/no attribute to boolean value. </summary>
        /// <param name="value"> The value. </param>
        /// <returns> True for yes, otherwise false. </returns>
        ///-------------------------------------------------------------------------------------------------
        private bool ConvertYesNo(String value)
        {
            return ((value != null) && (value.Equals("yes", StringComparison.OrdinalIgnoreCase)));
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Convert supported attribute to database boolean value. </summary>
        /// <param name="value">    The value. </param>
        /// <returns> True for supported, otherwise false. </returns>
        ///-------------------------------------------------------------------------------------------------
        private bool ConvertSupported(String value)
        {
            return ((value != null) && (value.Equals("supported", StringComparison.OrdinalIgnoreCase)));
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Parse year. </summary>
        /// <param name="yearStr">  The year string. </param>
        /// <returns> An int? </returns>
        ///-------------------------------------------------------------------------------------------------
        private int? ParseYear(String yearStr)
        {
            int year;
            if (Int32.TryParse(yearStr, out year))
                return year;
            else
                return null;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Populates the region column using external script. </summary>
        /// <param name="transaction"> The transaction. </param>
        ///-------------------------------------------------------------------------------------------------
        private void PopulateRegionColumn(SQLiteTransaction transaction)
        {
            // Run region queries.
            using (SQLiteCommand command = new SQLiteCommand(Properties.Resources.RegionQueries, m_connection, transaction))
                command.ExecuteNonQuery();
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Check schema version and rebuilds if out of date. </summary>
        /// <returns> True if schema must be rebuilt, else false. </returns>
        ///-------------------------------------------------------------------------------------------------
        private bool CheckAndRebuildSchema()
        {
            if (GetSchemaVersionID() != SqlConst.SCHEMA_ID)
            {
                BuildSchema();
                return true;
            }
            else
                return false;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the schema version identifier. </summary>
        /// <returns> The version identifier. </returns>
        ///-------------------------------------------------------------------------------------------------
        private int GetSchemaVersionID()
        {
            using (SQLiteCommand command = new SQLiteCommand("PRAGMA user_version", m_connection))
                return Convert.ToInt32(command.ExecuteScalar());
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Builds the schema. </summary>
        ///-------------------------------------------------------------------------------------------------
        private void BuildSchema()
        {
            OnStatusMessage(new StatusMessageEventArgs("(Re)Generating schema..."));

            using (SQLiteCommand command = m_connection.CreateCommand())
            {
                OnStatusMessage(new StatusMessageEventArgs("Beginning transaction..."));
                command.Transaction = m_connection.BeginTransaction();

                try
                {
                    OnStatusMessage(new StatusMessageEventArgs("Building table list..."));

                    // Build list of user tables, then drop them. Attached indexes will be dropped automatically.
                    List<String> tables = new List<string>();
                    command.CommandText = "select [name] from [sqlite_master] where [type] = 'table'";
                    using (SQLiteDataReader reader = command.ExecuteReader())
                        while (reader.Read())
                            tables.Add(reader.GetString(0));

                    foreach (String table in tables)
                    {
                        OnStatusMessage(new StatusMessageEventArgs(String.Format("Dropping table [{0}]...", table)));
                        command.CommandText = String.Format("drop table [{0}]", table);
                        command.ExecuteNonQuery();
                    }

                    OnStatusMessage(new StatusMessageEventArgs("Creating tables..."));

                    // Craete build table.
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("create table [{0}] (", SqlConst.BUILD_TABLE);
                    sb.AppendFormat(GetColumnDefinition(SqlConst.BUILD_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEBUG_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MAMECONFIG_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create games table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.GAME_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.PrimaryText));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SOURCE_FILE_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.IS_BIOS_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.IS_DEVICE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.IS_MECHANICAL_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.RUNNABLE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CLONE_OF_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.ROM_OF_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SAMPLE_OF_COL_, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DESCRIPTION_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.YEAR_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MANUFACTURER_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SOUND_CHANNELS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INPUT_SERVICE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INPUT_TILT_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INPUT_PLAYERS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INPUT_BUTTONS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INPUT_COINS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_STATUS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_EMULATION_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_COLOR_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_SOUND_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_GRAPHIC_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_COCKTAIL_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_PROTECTION_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_SAVESTATE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DRIVER_PALETTE_SIZE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CATEGORY_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SUB_CATEGORY_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.IS_MATURE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VERSION_ADDED_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CHD_REQUIRED, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.REGION_COL, SimpleDatatype.Text));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create parent table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.PARENT_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.PrimaryText));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create child table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.CLONE_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.PrimaryText));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.PARENT_COL, SimpleDatatype.TextNotNull));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create biosset table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.BIOSSET_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DESCRIPTION_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create roms table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.ROM_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.BIOS_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SIZE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CRC_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SHA1_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MERGE_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.REGION_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.OFFSET_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.STATUS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.OPTIONAL_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create disks table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DISK_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SHA1_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MERGE_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.REGION_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INDEX_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.WRITABLE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.STATUS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.OPTIONAL_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create device_refs table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DEVICE_REF_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create samples table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.SAMPLE_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create chips table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.CHIP_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TYPE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CLOCK_COL, SimpleDatatype.Text));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create display table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DISPLAY_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TYPE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.ROTATE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.FLIPX_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.WIDTH_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.HEIGHT_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.REFERSH_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.PIX_CLOCK_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.HTOTAL_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.HBEND_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.HBSTART_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VTOTAL_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VBEND_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VBSTART_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create controls table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.CONTROL_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TYPE_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MINIMUM_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MAXIMUM_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.SENSITIVITY_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.KEY_DELTA_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.REVERSE_COL, SimpleDatatype.Boolean));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.WAYS_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.WAYS2_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.WAYS3_COL, SimpleDatatype.Text));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create dips table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DIP_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MASK_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create dip values table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DIP_VALUE_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DIP_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VALUE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create config table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.CONFIG_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MASK_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create config setting table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.CONFIG_SETTING_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.CONFIG_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VALUE_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create ports table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.PORT_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.MASK_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create adjuster table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.ADJUSTER_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create devices table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.DEVICE_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TYPE_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.TAG_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.INTERFACE_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.BRIEF_NAME_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.EXTENSIONS_COL, SimpleDatatype.Text));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create slot table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.SLOT_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.OPTION_NAME_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEV_NAME_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Boolean));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create software lists table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.SOFTWARE_LIST_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.NAME_COL, SimpleDatatype.TextNotNull));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.STATUS_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.FILTER_COL, SimpleDatatype.Text));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Create ram options table.
                    sb.Clear();
                    sb.AppendFormat("create table [{0}] (", SqlConst.RAM_OPTION_TABLE);
                    sb.Append(GetColumnDefinition(SqlConst.GAME_NAME_COL, SimpleDatatype.Text));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.DEFAULT_COL, SimpleDatatype.Int));
                    sb.AppendFormat(", {0}", GetColumnDefinition(SqlConst.VALUE_COL, SimpleDatatype.Int));
                    sb.Append(")");

                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();

                    // Set schema version.
                    command.CommandText = String.Format("PRAGMA user_version = {0}", SqlConst.SCHEMA_ID.ToString());
                    command.ExecuteNonQuery();

                    OnStatusMessage(new StatusMessageEventArgs("Committing..."));
                    command.Transaction.Commit();
                    OnStatusMessage(new StatusMessageEventArgs("Complete."));
                }
                catch
                {
                    try
                    {
                        command.Transaction.Rollback();
                    }
                    catch
                    { }

                    throw;
                }
            } // End using command.
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets command used to insert into specified table. Assumes insert columns are in db
        /// order. </summary>
        /// <param name="tableName">   Name of the table to insert into. </param>
        /// <param name="columnCount"> Number of columns. </param>
        /// <param name="transaction"> The transaction. </param>
        /// <returns> The prepared insert command. </returns>
        ///-------------------------------------------------------------------------------------------------
        private SQLiteCommand GetInsertCommand(String tableName, int columnCount, SQLiteTransaction transaction)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("insert into [{0}] values (?", tableName);
            for (int i = 0; i < columnCount - 1; i++)
                sb.Append(", ?");

            sb.Append(")");

            SQLiteCommand command = new SQLiteCommand(sb.ToString(), m_connection, transaction);

            for (int i = 0; i < columnCount; i++)
                command.Parameters.Add(command.CreateParameter());

            command.Prepare();
            return command;
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Drops specified table using supplied command object. </summary>
        /// <param name="command">   The command. </param>
        /// <param name="tableName"> Name of the table to drop. </param>
        ///-------------------------------------------------------------------------------------------------
        private void DropTable(SQLiteCommand command, String tableName)
        {
            command.CommandText = String.Format("drop table if exists [{0}]", tableName);
            command.ExecuteNonQuery();
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Creates an index. </summary>
        /// <param name="tableName">   Name of the table. </param>
        /// <param name="column">      The column. </param>
        /// <param name="transaction"> The transaction. </param>
        ///-------------------------------------------------------------------------------------------------
        private void CreateIndex(String tableName, String column, SQLiteTransaction transaction)
        {
            CreateIndex(tableName, new String[] { column }, transaction);
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Creates an index. </summary>
        /// <param name="tableName">   Name of the table. </param>
        /// <param name="columns">     The column(s). </param>
        /// <param name="transaction"> The transaction. </param>
        ///-------------------------------------------------------------------------------------------------
        private void CreateIndex(String tableName, String[] columns, SQLiteTransaction transaction)
        {
            if ((columns == null) || (columns.Length <= 0))
                throw new Exception("One or more columns must be defined.");

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("{0}_{1}", tableName, columns[0]);
            for (int i = 1; i < columns.Length; i++)
                sb.AppendFormat("_{0}", columns[i]);

            String indexName = sb.ToString();

            sb.Clear();
            sb.AppendFormat("create index [{0}] on [{1}] ([{2}]", indexName, tableName, columns[0]);
            for (int i = 1; i < columns.Length; i++)
                sb.AppendFormat(", [{0}]", columns[i]);

            sb.Append(")");

            using (SQLiteCommand command = new SQLiteCommand(sb.ToString(), m_connection, transaction))
            {
                OnStatusMessage(new StatusMessageEventArgs(String.Format("Generating index on [{0}]...", tableName)));
                command.ExecuteNonQuery();
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets the number of items in database. </summary>
        /// <returns> The count. </returns>
        ///-------------------------------------------------------------------------------------------------
        private int GetCount()
        {
            using (SQLiteCommand command = m_connection.CreateCommand())
            {
                command.CommandText = String.Format("select count(*) from {0}", SqlConst.GAME_TABLE);
                return (Convert.ToInt32(command.ExecuteScalar()));
            }
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Retrieve connection string. </summary>
        /// <exception cref="ArgumentNullException"> Thrown when one or more required arguments are null. </exception>
        /// <param name="dbFilePath"> Full database file path. </param>
        /// <returns> The connection string. </returns>
        ///-------------------------------------------------------------------------------------------------
        private static String GetConnectionString(String dbFilePath)
        {
            if (dbFilePath == null)
                throw new ArgumentNullException("dbFilePath");

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();

            sb.DataSource = dbFilePath;
            sb.DateTimeFormat = SQLiteDateFormats.ISO8601;
            sb.DateTimeKind = DateTimeKind.Utc;
            sb.FailIfMissing = false;
            sb.JournalMode = SQLiteJournalModeEnum.Default;
            sb.LegacyFormat = false;
            sb.SyncMode = SynchronizationModes.Normal;
            sb.UseUTF16Encoding = false;

            return sb.ToString();
        }

        ///-------------------------------------------------------------------------------------------------
        /// <summary> Gets sql column definition. </summary>
        /// <exception cref="NotImplementedException"> Thrown when the requested operation is
        /// unimplemented. </exception>
        /// <param name="columnName"> Name of the column. </param>
        /// <param name="dataType">   Column type. </param>
        /// <returns> The sqlite column definition. </returns>
        ///-------------------------------------------------------------------------------------------------
        private static String GetColumnDefinition(String columnName, SimpleDatatype dataType)
        {
            switch (dataType)
            {
                case SimpleDatatype.PrimaryText:
                    return String.Format("[{0}] text not null primary key collate nocase", columnName);

                case SimpleDatatype.Text:
                    return String.Format("[{0}] text collate nocase", columnName);

                case SimpleDatatype.TextNotNull:
                    return String.Format("[{0}] text not null collate nocase", columnName);

                case SimpleDatatype.Int:
                    return String.Format("[{0}] int", columnName);

                case SimpleDatatype.Boolean:
                    return String.Format("[{0}] intbool", columnName);

                default:
                    throw new NotImplementedException();
            }
        }

        #endregion
    }
}
